﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CONEK911Manager.DTO;

namespace CONEK911Manager.DAO
{
    class ProductDAO
    {
        readonly string _connectionString = ConfigurationManager.ConnectionStrings["Conek911ConnectionString"].ConnectionString;
        public bool AddProduct(ProductDTO product)
        {
            try
            {
                using (var con = new SqlConnection(_connectionString))
                {
                    const string sql = "INSERT into [Product] (ProductCode, ProductName, Size, PV, Quantity, Price) VALUES (@code, @name, @size, @pv, @quantity, @price)";
                    using (var sqlAddProduct = new SqlCommand(sql))
                    {
                        sqlAddProduct.Connection = con;
                        sqlAddProduct.Parameters.Add(new SqlParameter("@code", product.Code));
                        sqlAddProduct.Parameters.Add(new SqlParameter("@name", product.Name));
                        sqlAddProduct.Parameters.Add(new SqlParameter("@size", product.Size));
                        sqlAddProduct.Parameters.Add(new SqlParameter("@pv", product.Pv));
                        sqlAddProduct.Parameters.Add(new SqlParameter("@quantity", product.Quantity));
                        sqlAddProduct.Parameters.Add(new SqlParameter("@price", product.Price));
                        con.Open();
                        var result = sqlAddProduct.ExecuteNonQuery();
                        con.Close();
                        if (result == 1)
                        {
                            return true;
                        }
                        return false;
                    }
                }
            } catch (Exception e)
            {
                try
                {
                    Logger.WriteLog(e.Message + "-" + e.StackTrace);
                }
                catch
                { }
                return false;
            }
        }

        public List<ProductDTO> GetAllProduct()
        {
            var listProduct = new List<ProductDTO>();
            using (var con = new SqlConnection(_connectionString))
            {
                const string sql = "SELECT * FROM [Product]";
                using (var cmd = new SqlCommand(sql))
                {
                    cmd.Connection = con;
                    con.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var product = new ProductDTO
                                              {
                                                  Code = Convert.ToString(reader["ProductCode"]),
                                                  Name = Convert.ToString(reader["ProductName"]),
                                                  Price = float.Parse(Convert.ToString(reader["Price"])),
                                                  Pv = float.Parse(Convert.ToString(reader["PV"])),
                                                  Quantity = float.Parse(Convert.ToString(reader["Quantity"])),
                                                  Size = Convert.ToString(reader["Size"])
                                              };
                            listProduct.Add(product);
                        }
                    }
                    con.Close();
                }
            }
            
            return listProduct;
        }

        public bool RemoveProduct(string productCode)
        {
            try
            {
                using (var con = new SqlConnection(_connectionString))
                {
                    const string sql = "DELETE from [Product] WHERE ProductCode = @code";
                    using (var sqlAddProduct = new SqlCommand(sql))
                    {
                        sqlAddProduct.Connection = con;
                        sqlAddProduct.Parameters.Add(new SqlParameter("@code", productCode));
                        con.Open();
                        var result = sqlAddProduct.ExecuteNonQuery();
                        con.Close();
                        if (result == 1)
                        {
                            return true;
                        }
                        return false;
                    }
                }
            }
            catch (Exception e)
            {
                try
                {
                    Logger.WriteLog(e.Message + "-" + e.StackTrace);
                }
                catch
                { }
                return false;
            }
        }

        public bool UpdateProduct(ProductDTO product)
        {
            try
            {
                using (var con = new SqlConnection(_connectionString))
                {
                    const string sql = "UPDATE [Product] "
                                     + "SET ProductName = @name, Size = @size, PV = @pv, Quantity = @quantity, Price = @price "
                                     + "WHERE ProductCode = @code";
                    using (var sqlUpdateProduct = new SqlCommand(sql))
                    {
                        sqlUpdateProduct.Connection = con;
                        sqlUpdateProduct.Parameters.Add(new SqlParameter("@code", product.Code));
                        sqlUpdateProduct.Parameters.Add(new SqlParameter("@name", product.Name));
                        sqlUpdateProduct.Parameters.Add(new SqlParameter("@size", product.Size));
                        sqlUpdateProduct.Parameters.Add(new SqlParameter("@pv", product.Pv));
                        sqlUpdateProduct.Parameters.Add(new SqlParameter("@quantity", product.Quantity));
                        sqlUpdateProduct.Parameters.Add(new SqlParameter("@price", product.Price));
                        con.Open();
                        var result = sqlUpdateProduct.ExecuteNonQuery();
                        con.Close();
                        if (result == 1)
                        {
                            return true;
                        }
                        return false;
                    }
                }
            }
            catch (Exception e)
            {
                try
                {
                    Logger.WriteLog(e.Message + "-" + e.StackTrace);
                }
                catch
                { }
                return false;
            }
        }
    }
}
